Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

Interfacing with RDBMS stored procedures

As previously mentioned, the RUN STORED-PROCEDURE statement is the initial statement required for the execution of all stored procedures you define and initiate through Progress. It can run an RDBMS stored procedure bound by the data source, or allow you to send SQL to an ORACLE Server data source using an OpenEdge DataServer. The specific, additional keywords and elements you must include in a stored procedure or define for a send-sql statement depend on the stored procedure characteristics including its signature, data results, and methods chosen to retrieve output to the 4GL procedure.

This section identifies and describes the syntax elements, keywords, and other requirements associated with obtaining stored procedure output from a stored procedure data request.

The following sections describe how to run ORACLE stored procedures and retrieve return codes, output parameter values, and results sets.

Example 3–3 is the basis for the following examples of how to run stored procedures in the Progress 4GL using this stored procedure created in ORACLE.

CREATE PROCEDURE pcust (num IN INT, orders OUT INT, states OUT INT) AS BEGIN IF num IS NULL THEN raise_application_error (-20101, ’Cust Num is missing’); ELSE SELECT COUNT (*) INTO orders FROM customer, order_ WHERE customer.Cust_num = order_.Cust_num AND customer.Cust_num > num; SELECT count(*) INTO states FROM customer WHERE cust_num > num; END IF; END;

Example 3–3: ORACLE stored procedure pcust

This PL/SQL code creates the stored procedure pcust and defines three parameters: num, orders, and states. The orders and states parameters are output parameters, which means that the procedure returns values for these parameters to the caller. All the parameters are of the data type INTEGER.

Note: Typically, you can have only fifty stored procedures running at one time. This number, however, is further restricted by the number of open cursors you specified for your ORACLE database or for the current session. See the "Index cursors" section for information on specifying open cursors. Cursor limitations also vary across platforms. See your ORACLE documentation for more information.